"""
主GUI界面
优化的分类展示Meiya SQL生成工具界面
"""

import customtkinter as ctk
import tkinter.filedialog as filedialog
import tkinter.messagebox as messagebox
import json
import os
import threading
from typing import Optional, Dict, List, Any
from collections import defaultdict

from constants import GUIConfig, Messages
from excel_parser import ExcelParser


class ConfigManager:
    """配置管理器，用于保存和读取用户配置"""
    
    @staticmethod
    def load_config() -> dict:
        """加载配置文件"""
        config_path = GUIConfig.get_config_path()
        default_config = {
            "last_directory": os.path.expanduser("~"),
            "last_save_dir": os.path.expanduser("~"),
            "recent_tasks": [],  # 最近使用的任务名称列表
            "recent_names": []   # 最近使用的名字缩写列表
        }
        
        try:
            if os.path.exists(config_path):
                with open(config_path, 'r', encoding='utf-8') as f:
                    loaded_config = json.load(f)
                    # 确保所有默认配置项都存在
                    for key, value in default_config.items():
                        if key not in loaded_config:
                            loaded_config[key] = value
                    return loaded_config
        except Exception as e:
            print(f"加载配置文件失败: {e}")
        
        return default_config
    
    @staticmethod
    def save_config(config: dict) -> None:
        """保存配置文件"""
        config_path = GUIConfig.get_config_path()
        try:
            # 确保目录存在
            os.makedirs(os.path.dirname(config_path), exist_ok=True)
            with open(config_path, 'w', encoding='utf-8') as f:
                json.dump(config, f, ensure_ascii=False, indent=2)
        except Exception as e:
            print(f"保存配置文件失败: {e}")
            
    @staticmethod
    def add_recent_item(config: dict, item_type: str, value: str, max_items: int = 10) -> None:
        """添加最近使用的项目到配置中
        
        Args:
            config: 配置字典
            item_type: 项目类型，如'recent_tasks'或'recent_names'
            value: 要添加的值
            max_items: 最大保存数量
        """
        if not value:
            return
            
        # 确保配置项存在
        if item_type not in config:
            config[item_type] = []
            
        # 如果已存在，先移除旧值
        if value in config[item_type]:
            config[item_type].remove(value)
            
        # 添加到列表开头
        config[item_type].insert(0, value)
        
        # 限制列表长度
        config[item_type] = config[item_type][:max_items]


class MeiyaSQLGenerator:
    """Meiya SQL生成工具主界面类"""
    
    def __init__(self):
        # 获取屏幕尺寸
        import tkinter as tk
        temp_root = tk.Tk()
        screen_width = temp_root.winfo_screenwidth()
        screen_height = temp_root.winfo_screenheight()
        temp_root.destroy()
        
        # 存储屏幕信息
        self.screen_width = screen_width
        self.screen_height = screen_height
        
        # 动态计算窗口大小（默认使用屏幕的85%）
        window_width = int(screen_width * 0.85)
        window_height = int(screen_height * 0.85)
        
        # 最小尺寸限制
        min_width = 1000
        min_height = 700
        
        # DPI自适应缩放
        if screen_width >= 2560:  # 4K显示器
            scale_factor = 1.2
        elif screen_width >= 1920:  # 2K显示器  
            scale_factor = 1.1
        elif screen_width <= 1366:  # 小屏幕
            scale_factor = 0.9
        else:
            scale_factor = 1.0
        
        # 设置缩放
        ctk.set_widget_scaling(scale_factor)
        ctk.set_window_scaling(scale_factor)
        
        # 初始化customtkinter
        ctk.set_appearance_mode("dark")
        ctk.set_default_color_theme("blue")
        
        # 创建主窗口
        self.root = ctk.CTk()
        self.root.title("🚀 Meiya SQL生成工具")
        self.root.geometry(f"{window_width}x{window_height}")
        self.root.minsize(min_width, min_height)
        
        # 支持全屏
        self.is_fullscreen = False
        self.root.bind("<F11>", self._toggle_fullscreen)
        self.root.bind("<Escape>", self._end_fullscreen)
        
        # 居中显示窗口
        self._center_window()
        
        # 初始化变量
        self.config = ConfigManager.load_config()
        self.selected_file_path = ""
        self.excel_parser = ExcelParser()
        self.current_step = 1
        self.parsed_data = []
        self.grouped_data = {}
        self.current_database = ""
        self.current_requirement = ""
        self.available_sheets = []
        self.selected_sheet = ""
        
        # 创建界面
        self._create_widgets()
        
        # 绑定窗口大小变化事件
        self.root.bind("<Configure>", self._on_window_resize)
        
        # 绑定窗口关闭事件
        self.root.protocol("WM_DELETE_WINDOW", self._on_closing)
    
    def _center_window(self):
        """将窗口居中显示"""
        self.root.update_idletasks()
        width = self.root.winfo_width()
        height = self.root.winfo_height()
        x = (self.root.winfo_screenwidth() // 2) - (width // 2)
        y = (self.root.winfo_screenheight() // 2) - (height // 2)
        self.root.geometry(f"{width}x{height}+{x}+{y}")
        
    def _toggle_fullscreen(self, event=None):
        """切换全屏模式"""
        self.is_fullscreen = not self.is_fullscreen
        self.root.attributes("-fullscreen", self.is_fullscreen)
        return "break"
        
    def _end_fullscreen(self, event=None):
        """退出全屏模式"""
        self.is_fullscreen = False
        self.root.attributes("-fullscreen", False)
        return "break"
        
    def _on_window_resize(self, event=None):
        """窗口大小变化时的处理"""
        if event and event.widget == self.root:
            # 只处理主窗口的大小变化
            
            # 1. 调整主操作面板宽度
            if hasattr(self, 'left_frame'):
                # 动态调整左侧面板宽度 - 窗口宽度的25%，最小280px
                new_width = max(280, int(self.root.winfo_width() * 0.25))
                # 找到左侧容器并调整宽度
                for child in self.main_frame.winfo_children():
                    if hasattr(child, 'winfo_children'):
                        for subchild in child.winfo_children():
                            if isinstance(subchild, ctk.CTkFrame) and subchild.winfo_width() < self.root.winfo_width() * 0.5:
                                subchild.configure(width=new_width)
                                break
            
            # 2. 调整数据库视图中的需求列表宽度
            if self.db_tabview:
                # 计算新的需求列表宽度
                req_list_width = max(180, int(self.root.winfo_width() * 0.15))
                
                # 遍历所有数据库选项卡
                for tab_name in self.db_tabview._tab_dict:
                    tab_frame = self.db_tabview._tab_dict[tab_name]
                    
                    # 查找需求列表框架
                    for child in tab_frame.winfo_children():
                        if isinstance(child, ctk.CTkFrame):
                            for sub_child in child.winfo_children():
                                if isinstance(sub_child, ctk.CTkFrame) and sub_child.winfo_width() < self.root.winfo_width() * 0.3:
                                    # 这很可能是左侧需求列表框架
                                    sub_child.configure(width=req_list_width)
    
    def _create_widgets(self):
        """创建所有界面组件"""
        # 创建主容器
        self.main_frame = ctk.CTkFrame(self.root, fg_color="transparent")
        self.main_frame.pack(fill="both", expand=True, padx=20, pady=20)
        
        # 创建顶部标题区域
        self._create_compact_header()
        
        # 创建主要内容区域
        self._create_main_content()
    
    def _create_compact_header(self):
        """创建紧凑的标题区域"""
        header_frame = ctk.CTkFrame(
            self.main_frame,
            height=80,
            corner_radius=12,
            fg_color=("#2b5ce6", "#1f4cbf")
        )
        header_frame.pack(fill="x", pady=(0, 15))
        header_frame.pack_propagate(False)
        
        # 左侧标题信息
        title_container = ctk.CTkFrame(header_frame, fg_color="transparent")
        title_container.pack(side="left", fill="y", padx=25, pady=15)
        
        title_label = ctk.CTkLabel(
            title_container,
            text="🚀 Meiya SQL生成工具",
            font=ctk.CTkFont(family="Microsoft YaHei", size=24, weight="bold"),
            text_color="#ffffff"
        )
        title_label.pack(anchor="w")
        
        subtitle_label = ctk.CTkLabel(
            title_container,
            text="需求号 → Excel解析 → 分类展示 → SQL生成",
            font=ctk.CTkFont(family="Microsoft YaHei", size=13),
            text_color="#e6f1ff"
        )
        subtitle_label.pack(anchor="w", pady=(2, 0))
        
        # 右侧步骤指示器
        self._create_step_indicator(header_frame)
    
    def _create_step_indicator(self, parent):
        """创建步骤指示器"""
        step_frame = ctk.CTkFrame(parent, fg_color="transparent")
        step_frame.pack(side="right", padx=25, pady=15)
        
        steps = ["选择文件", "选择Sheet页", "解析数据", "分类展示"]
        self.step_labels = []
        
        for i, step in enumerate(steps, 1):
            step_container = ctk.CTkFrame(step_frame, fg_color="transparent")
            step_container.pack(side="left", padx=8)
            
            # 步骤圆圈
            circle_color = "#28a745" if i <= self.current_step else "#6c757d"
            step_circle = ctk.CTkLabel(
                step_container,
                text=str(i),
                font=ctk.CTkFont(family="Microsoft YaHei", size=14, weight="bold"),
                text_color="#ffffff",
                fg_color=circle_color,
                corner_radius=15,
                width=30,
                height=30
            )
            step_circle.pack()
            
            # 步骤文字
            step_label = ctk.CTkLabel(
                step_container,
                text=step,
                font=ctk.CTkFont(family="Microsoft YaHei", size=11),
                text_color="#ffffff" if i <= self.current_step else "#cccccc"
            )
            step_label.pack(pady=(2, 0))
            
            self.step_labels.append((step_circle, step_label))
    
    def _create_main_content(self):
        """创建主要内容区域"""
        # 创建左右分栏布局
        content_frame = ctk.CTkFrame(self.main_frame, fg_color="transparent")
        content_frame.pack(fill="both", expand=True)
        
        # 左侧操作区域（紧凑布局）
        left_width = max(260, int(self.root.winfo_width() * 0.22))  # 窗口宽度的22%，最小260px
        self.left_frame = ctk.CTkFrame(content_frame, width=left_width, corner_radius=12)
        self.left_frame.pack(side="left", fill="y", padx=(0, 15))
        self.left_frame.pack_propagate(False)
        
        # 右侧结果区域（切换式）
        right_frame = ctk.CTkFrame(content_frame, corner_radius=12)
        right_frame.pack(side="right", fill="both", expand=True)
        
        # 创建左侧操作面板
        self._create_operation_panel(self.left_frame)
        
        # 创建右侧结果面板
        self._create_result_panel(right_frame)
    
    def _create_operation_panel(self, parent):
        """创建左侧操作面板"""
        # 文件选择区域
        file_section = ctk.CTkFrame(parent, fg_color="transparent")
        file_section.pack(fill="x", padx=15, pady=(15, 8))
        
        step1_label = ctk.CTkLabel(
            file_section,
            text="📁 步骤1: 选择Excel文件",
            font=ctk.CTkFont(family="Microsoft YaHei", size=16, weight="bold"),
            text_color=("#2c5aa0", "#4a9eff")
        )
        step1_label.pack(anchor="w", pady=(0, 8))
        
        # 文件路径显示
        self.file_path_var = ctk.StringVar(value="请选择Excel文件...")
        self.file_path_entry = ctk.CTkEntry(
            file_section,
            textvariable=self.file_path_var,
            state="readonly",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12),
            height=40
        )
        self.file_path_entry.pack(fill="x", pady=(0, 8))
        
        # 浏览按钮
        self.browse_button = ctk.CTkButton(
            file_section,
            text="🔍 浏览选择文件",
            command=self._browse_file,
            font=ctk.CTkFont(family="Microsoft YaHei", size=13, weight="bold"),
            height=38,
            corner_radius=8
        )
        self.browse_button.pack(fill="x")
        
        # 分隔线
        separator1 = ctk.CTkFrame(parent, height=2, fg_color=("#d0d0d0", "#505050"), corner_radius=1)
        separator1.pack(fill="x", padx=25, pady=12)
        
        # Sheet页选择区域（直接显示）
        self.sheet_section = ctk.CTkFrame(parent, fg_color="transparent")
        self.sheet_section.pack(fill="x", padx=15, pady=8)
        
        step_sheet_label = ctk.CTkLabel(
            self.sheet_section,
            text="📋 步骤2: 选择Sheet页",
            font=ctk.CTkFont(family="Microsoft YaHei", size=16, weight="bold"),
            text_color=("#2d7d32", "#4caf50")
        )
        step_sheet_label.pack(anchor="w", pady=(0, 8))
        
        # Sheet页选择下拉框
        self.sheet_combobox = ctk.CTkComboBox(
            self.sheet_section,
            values=["请先选择Excel文件"],
            font=ctk.CTkFont(family="Microsoft YaHei", size=12),
            height=36,
            state="disabled",  # 初始禁用，选择文件后启用
            command=self._on_sheet_selected,
            dropdown_font=ctk.CTkFont(family="Microsoft YaHei", size=12)
        )
        self.sheet_combobox.set("请先选择Excel文件")
        self.sheet_combobox.pack(fill="x", pady=(0, 8))
        
        # 分隔线2
        separator2 = ctk.CTkFrame(parent, height=2, fg_color=("#d0d0d0", "#505050"), corner_radius=1)
        separator2.pack(fill="x", padx=25, pady=12)
        
        # 解析操作区域
        parse_section = ctk.CTkFrame(parent, fg_color="transparent")
        parse_section.pack(fill="x", padx=15, pady=8)
        
        step3_label = ctk.CTkLabel(
            parse_section,
            text="⚡ 步骤3: 解析处理",
            font=ctk.CTkFont(family="Microsoft YaHei", size=16, weight="bold"),
            text_color=("#f57c00", "#ff9800")
        )
        step3_label.pack(anchor="w", pady=(0, 8))
        
        # 解析按钮
        self.parse_button = ctk.CTkButton(
            parse_section,
            text="🚀 开始解析Excel",
            command=self._parse_excel,
            font=ctk.CTkFont(family="Microsoft YaHei", size=14, weight="bold"),
            height=42,
            corner_radius=8,
            fg_color=("#28a745", "#20c997"),
            hover_color=("#218838", "#17a2b8"),
            state="disabled"
        )
        self.parse_button.pack(fill="x", pady=(0, 8))
        
        # 进度条
        self.progress_bar = ctk.CTkProgressBar(parse_section, height=12, corner_radius=6)
        self.progress_bar.pack(fill="x", pady=(0, 4))
        self.progress_bar.set(0)
        
        # 进度文字
        self.progress_label = ctk.CTkLabel(
            parse_section,
            text="🟢 就绪",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12, weight="bold"),
            text_color=("#28a745", "#20c997")
        )
        self.progress_label.pack(anchor="w")
        
        # 分隔线
        separator3 = ctk.CTkFrame(parent, height=2, fg_color=("#d0d0d0", "#505050"), corner_radius=1)
        separator3.pack(fill="x", padx=25, pady=12)
        
        # 打包操作区域
        package_section = ctk.CTkFrame(parent, fg_color="transparent")
        package_section.pack(fill="x", padx=15, pady=8)
        
        step4_label = ctk.CTkLabel(
            package_section,
            text="📦 步骤4: 打包生成",
            font=ctk.CTkFont(family="Microsoft YaHei", size=16, weight="bold"),
            text_color=("#7b1fa2", "#9c27b0")
        )
        step4_label.pack(anchor="w", pady=(0, 8))
        
        # 打包参数输入
        params_frame = ctk.CTkFrame(package_section, corner_radius=8)
        params_frame.pack(fill="x", pady=(0, 8))
        
        # 编号输入
        number_frame = ctk.CTkFrame(params_frame, fg_color="transparent")
        number_frame.pack(fill="x", padx=12, pady=(12, 4))
        
        number_label = ctk.CTkLabel(
            number_frame,
            text="编号:",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12, weight="bold"),
            width=60
        )
        number_label.pack(side="left")
        
        # 编号输入和取号按钮
        number_input_frame = ctk.CTkFrame(number_frame, fg_color="transparent")
        number_input_frame.pack(side="right", fill="x", expand=True, padx=(10, 0))
        
        self.number_entry = ctk.CTkEntry(
            number_input_frame,
            placeholder_text="两位数编号，如: 01",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12),
            height=32
        )
        self.number_entry.pack(side="left", fill="x", expand=True)
        
        self.get_number_button = ctk.CTkButton(
            number_input_frame,
            text="🌐 取号",
            command=self._open_number_browser,
            font=ctk.CTkFont(family="Microsoft YaHei", size=10),
            width=60,
            height=32
        )
        self.get_number_button.pack(side="right", padx=(5, 0))
        
        # 任务输入（带下拉历史记录）
        task_frame = ctk.CTkFrame(params_frame, fg_color="transparent")
        task_frame.pack(fill="x", padx=12, pady=4)
        
        task_label = ctk.CTkLabel(
            task_frame,
            text="任务:",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12, weight="bold"),
            width=60
        )
        task_label.pack(side="left")
        
        task_input_frame = ctk.CTkFrame(task_frame, fg_color="transparent")
        task_input_frame.pack(side="right", fill="x", expand=True, padx=(10, 0))
        
        self.task_entry = ctk.CTkEntry(
            task_input_frame,
            placeholder_text="例如: task001",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12),
            height=32
        )
        self.task_entry.pack(side="left", fill="x", expand=True)
        
        # 历史任务下拉按钮
        self.task_history_button = ctk.CTkButton(
            task_input_frame,
            text="▼",
            command=self._show_task_history,
            font=ctk.CTkFont(family="Microsoft YaHei", size=10),
            width=30,
            height=32
        )
        self.task_history_button.pack(side="right", padx=(5, 0))
        
        # 名字缩写输入（带下拉历史记录）
        name_frame = ctk.CTkFrame(params_frame, fg_color="transparent")
        name_frame.pack(fill="x", padx=12, pady=4)
        
        name_label = ctk.CTkLabel(
            name_frame,
            text="缩写:",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12, weight="bold"),
            width=60
        )
        name_label.pack(side="left")
        
        name_input_frame = ctk.CTkFrame(name_frame, fg_color="transparent")
        name_input_frame.pack(side="right", fill="x", expand=True, padx=(10, 0))
        
        self.name_entry = ctk.CTkEntry(
            name_input_frame,
            placeholder_text="例如: abc",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12),
            height=32
        )
        self.name_entry.pack(side="left", fill="x", expand=True)
        
        # 历史缩写下拉按钮
        self.name_history_button = ctk.CTkButton(
            name_input_frame,
            text="▼",
            command=self._show_name_history,
            font=ctk.CTkFont(family="Microsoft YaHei", size=10),
            width=30,
            height=32
        )
        self.name_history_button.pack(side="right", padx=(5, 0))
        
        # 环境选择
        env_frame = ctk.CTkFrame(params_frame, fg_color="transparent")
        env_frame.pack(fill="x", padx=12, pady=4)
        
        env_label = ctk.CTkLabel(
            env_frame,
            text="环境:",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12, weight="bold"),
            width=60
        )
        env_label.pack(side="left")
        
        self.env_var = ctk.StringVar(value="uat")
        env_radio_frame = ctk.CTkFrame(env_frame, fg_color="transparent")
        env_radio_frame.pack(side="right", fill="x", expand=True, padx=(10, 0))
        
        self.uat_radio = ctk.CTkRadioButton(
            env_radio_frame,
            text="UAT",
            variable=self.env_var,
            value="uat",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12)
        )
        self.uat_radio.pack(side="left", padx=(0, 20))
        
        self.prod_radio = ctk.CTkRadioButton(
            env_radio_frame,
            text="PROD",
            variable=self.env_var,
            value="prod",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12)
        )
        self.prod_radio.pack(side="left")
        
        # 保存位置选择
        save_frame = ctk.CTkFrame(params_frame, fg_color="transparent")
        save_frame.pack(fill="x", padx=12, pady=(4, 12))
        
        save_label = ctk.CTkLabel(
            save_frame,
            text="位置:",
            font=ctk.CTkFont(family="Microsoft YaHei", size=12, weight="bold"),
            width=60
        )
        save_label.pack(side="left")
        
        save_input_frame = ctk.CTkFrame(save_frame, fg_color="transparent")
        save_input_frame.pack(side="right", fill="x", expand=True, padx=(10, 0))
        
        self.save_path_var = ctk.StringVar(value="选择保存位置...")
        self.save_path_entry = ctk.CTkEntry(
            save_input_frame,
            textvariable=self.save_path_var,
            font=ctk.CTkFont(family="Microsoft YaHei", size=12),
            height=32,
            state="readonly"
        )
        self.save_path_entry.pack(side="left", fill="x", expand=True)
        
        self.browse_save_button = ctk.CTkButton(
            save_input_frame,
            text="📁 浏览",
            command=self._browse_save_location,
            font=ctk.CTkFont(family="Microsoft YaHei", size=10),
            width=60,
            height=32
        )
        self.browse_save_button.pack(side="right", padx=(5, 0))
        
        # 打包按钮
        self.package_button = ctk.CTkButton(
            package_section,
            text="📦 生成打包文件",
            command=self._on_package_click,
            font=ctk.CTkFont(family="Microsoft YaHei", size=14, weight="bold"),
            height=42,
            corner_radius=8,
            fg_color=("#1f9e89", "#0d7f70"),
            hover_color=("#17a085", "#0a6b5d"),
            state="disabled"  # 初始禁用
        )
        self.package_button.pack(fill="x", pady=(0, 8))
        
        # 底部操作按钮
        bottom_section = ctk.CTkFrame(parent, fg_color="transparent")
        bottom_section.pack(fill="x", side="bottom", padx=15, pady=15)
        
        button_container = ctk.CTkFrame(bottom_section, fg_color="transparent")
        button_container.pack(fill="x")
        
        # 重置按钮
        self.clear_button = ctk.CTkButton(
            button_container,
            text="🔄 重置",
            command=self._clear_all,
            font=ctk.CTkFont(family="Microsoft YaHei", size=14),
            height=40,
            width=100,
            corner_radius=8,
            fg_color=("#6c757d", "#5a6268")
        )
        self.clear_button.pack(side="left")
        
        # 帮助按钮
        help_button = ctk.CTkButton(
            button_container,
            text="❓ 帮助",
            command=self._show_help,
            font=ctk.CTkFont(family="Microsoft YaHei", size=14),
            height=40,
            width=100,
            corner_radius=8,
            fg_color=("#17a2b8", "#138496")
        )
        help_button.pack(side="right")
    

    def _create_result_panel(self, parent):
        """创建右侧结果面板"""
        # 标题区域
        title_frame = ctk.CTkFrame(parent, height=60, corner_radius=12)
        title_frame.pack(fill="x", padx=15, pady=(15, 10))
        title_frame.pack_propagate(False)
        
        title_label = ctk.CTkLabel(
            title_frame,
            text="📋 步骤4: 解析结果",
            font=ctk.CTkFont(family="Microsoft YaHei", size=18, weight="bold")
        )
        title_label.pack(side="left", padx=20, pady=20)
        
        # 统计信息显示（初始隐藏）
        self.stats_display = ctk.CTkLabel(
            title_frame,
            text="",
            font=ctk.CTkFont(family="Microsoft YaHei", size=13),
            text_color=("#666666", "#cccccc")
        )
        self.stats_display.pack(side="right", padx=20, pady=20)
        
        # 初始显示区域
        self.initial_display = ctk.CTkFrame(parent, corner_radius=12)
        self.initial_display.pack(fill="both", expand=True, padx=15, pady=(0, 15))
        
        welcome_text = """🎉 欢迎使用 Meiya SQL生成工具！

📋 使用步骤:
1️⃣ 点击左侧"浏览选择文件"选择Excel文件
2️⃣ 选择要处理的Sheet页（支持多Sheet页Excel）
3️⃣ 点击"开始解析Excel"处理数据  
4️⃣ 解析完成后，右侧会显示按数据库分类的结果

📄 新版Excel文件格式要求:
• 必须包含6列: 需求号、数据库、脚本类别、SQL脚本、备注、RollBack脚本
• 需求号: 需求编号（必填，新增字段）
• 数据库类型: iclaim, cclaim, adapter
• 脚本类别: DDL, DML

✨ 新增功能特性:
• 支持多Sheet页Excel文件，可选择指定的Sheet页进行处理
• 自动按数据库分类展示（iclaim、cclaim、adapter）
• 按需求号分组显示DDL/DML统计
• 点击需求号查看格式化SQL脚本
• 支持切换查看RollBack脚本

💡 提示: 选择文件后会自动显示所有可用的Sheet页供选择"""

        self.welcome_text = ctk.CTkTextbox(
            self.initial_display,
            font=ctk.CTkFont(family="Microsoft YaHei", size=13),
            wrap="word"
        )
        self.welcome_text.pack(fill="both", expand=True, padx=20, pady=20)
        self.welcome_text.insert("0.0", welcome_text)
        self.welcome_text.configure(state="disabled")
        
        # 数据库选项卡（初始隐藏）
        self.db_tabview = None
    
    def _browse_file(self):
        """浏览选择Excel文件"""
        initial_dir = self.config.get("last_directory", os.path.expanduser("~"))
        
        file_path = filedialog.askopenfilename(
            title="选择Excel文件 - Meiya SQL生成工具",
            initialdir=initial_dir,
            filetypes=GUIConfig.SUPPORTED_FORMATS
        )
        
        if file_path:
            self.selected_file_path = file_path
            filename = os.path.basename(file_path)
            self.file_path_var.set(f"✅ {filename}")
            
            # 更新配置
            self.config["last_directory"] = os.path.dirname(file_path)
            ConfigManager.save_config(self.config)
            
            # 获取并显示Sheet页列表
            self._load_sheet_names()
            
            # 更新步骤指示器
            self._update_step_indicator(2)
            self.progress_label.configure(text="🟡 文件已选择，请选择Sheet页")
    
    def _load_sheet_names(self):
        """加载Excel文件的Sheet页名称"""
        if not self.selected_file_path:
            return
            
        success, message, sheet_names = self.excel_parser.get_sheet_names(self.selected_file_path)
        
        if success:
            self.available_sheets = sheet_names
            
            # 更新下拉框选项并启用
            self.sheet_combobox.configure(values=sheet_names, state="readonly")
            if sheet_names:
                # 默认选择第一个sheet页
                self.sheet_combobox.set(sheet_names[0])
                self.selected_sheet = sheet_names[0]
                # 启用解析按钮
                self.parse_button.configure(state="normal")
                self._update_step_indicator(3)
                self.progress_label.configure(text="🟡 已选择Sheet页，可以开始解析")
            else:
                self.sheet_combobox.set("没有找到Sheet页")
                self.sheet_combobox.configure(state="disabled")
        else:
            messagebox.showerror("❌ 错误", f"获取Sheet页失败:\n{message}")
            self.sheet_combobox.set("获取Sheet页失败")
            self.sheet_combobox.configure(state="disabled")
    
    def _on_sheet_selected(self, selected_sheet):
        """Sheet页选择事件处理"""
        if selected_sheet and selected_sheet in self.available_sheets:
            self.selected_sheet = selected_sheet
            # 启用解析按钮
            self.parse_button.configure(state="normal")
            self._update_step_indicator(3)
            self.progress_label.configure(text="🟡 已选择Sheet页，可以开始解析")
        else:
            # 如果选择的sheet页无效，禁用解析按钮
            self.selected_sheet = ""
            self.parse_button.configure(state="disabled")
            self.progress_label.configure(text="⚠️ 请选择有效的Sheet页")
    
    def _parse_excel(self):
        """解析Excel文件"""
        if not self.selected_file_path:
            messagebox.showwarning("⚠️ 警告", "请先选择Excel文件！")
            return
            
        if not self.selected_sheet:
            messagebox.showwarning("⚠️ 警告", "请先选择Sheet页！")
            return
        
        threading.Thread(target=self._parse_excel_thread, daemon=True).start()
    
    def _parse_excel_thread(self):
        """后台解析线程"""
        try:
            self.root.after(0, lambda: self._set_parsing_state(True))
            
            success, message, data = self.excel_parser.parse_excel(self.selected_file_path, self.selected_sheet)
            
            if success:
                self.root.after(0, lambda: self._on_parse_success(data))
                print("\n" + "="*80)
                print("🚀 Meiya SQL生成工具 - 详细解析报告")
                print("="*80)
                self.excel_parser.print_data_summary()
                print("="*80)
            else:
                self.root.after(0, lambda: self._on_parse_error(message))
        
        except Exception as e:
            error_msg = f"解析过程中发生错误: {str(e)}"
            self.root.after(0, lambda: self._on_parse_error(error_msg))
    
    def _set_parsing_state(self, parsing: bool):
        """设置解析状态"""
        if parsing:
            self.parse_button.configure(state="disabled", text="⏳ 解析中...")
            self.browse_button.configure(state="disabled")
            self.clear_button.configure(state="disabled")
            self.sheet_combobox.configure(state="disabled")  # 解析时禁用sheet页选择
            self.progress_bar.set(0.6)
            self.progress_label.configure(text="🔄 正在解析Excel文件...", text_color=("#ffc107", "#ffcd39"))
        else:
            self.parse_button.configure(state="normal", text="🚀 开始解析Excel")
            self.browse_button.configure(state="normal")
            self.clear_button.configure(state="normal")
            # 恢复sheet页选择状态 - 如果有可用的sheet页才启用
            if self.available_sheets:
                self.sheet_combobox.configure(state="readonly")
            else:
                self.sheet_combobox.configure(state="disabled")
            self.progress_bar.set(0)
    
    def _on_parse_success(self, data):
        """解析成功处理"""
        self._set_parsing_state(False)
        self.progress_bar.set(1.0)
        self.progress_label.configure(text="✅ 解析完成", text_color=("#28a745", "#20c997"))
        self._update_step_indicator(4)
        
        # 保存解析数据
        self.parsed_data = data
        self._group_data_by_database()
        
        # 更新统计信息
        self._update_database_stats()
        
        # 创建数据库选项卡
        self._create_database_tabs()
        
        messagebox.showinfo("🎉 解析成功", f"Excel解析完成！\n共处理 {len(data)} 条记录\n已按数据库分类展示")
    
    def _group_data_by_database(self):
        """按数据库分组数据"""
        self.grouped_data = defaultdict(lambda: defaultdict(list))
        
        for item in self.parsed_data:
            database = item['database']
            requirement_no = item['requirement_no']
            self.grouped_data[database][requirement_no].append(item)
    
    def _update_database_stats(self):
        """更新数据库统计信息"""
        if not self.grouped_data:
            return
        
        # 计算总数
        total_dbs = len(self.grouped_data)
        total_reqs = sum(len(reqs) for reqs in self.grouped_data.values())
        total_records = len(self.parsed_data)
        
        # 更新右侧标题栏的统计显示
        stats_summary = f"📊 {total_dbs}个数据库 | {total_reqs}个需求 | {total_records}条记录"
        self.stats_display.configure(text=stats_summary)
        
        # 启用打包按钮
        self.package_button.configure(state="normal")
    
    def _create_database_tabs(self):
        """创建数据库选项卡"""
        # 隐藏初始显示
        self.initial_display.pack_forget()
        
        # 如果已经有选项卡，先销毁
        if self.db_tabview:
            self.db_tabview.destroy()
        
        # 创建数据库选项卡 - 蓝绿色调
        self.db_tabview = ctk.CTkTabview(
            self.initial_display.master, 
            corner_radius=12,
            border_width=2,
            segmented_button_selected_color=("#1f9e89", "#0d7f70"),
            segmented_button_selected_hover_color=("#17a085", "#0a6b5d"),
            command=self._on_database_tab_changed
        )
        self.db_tabview.pack(fill="both", expand=True, padx=15, pady=(0, 15))
        
        # 为每个数据库创建选项卡 - 简洁的显示名称
        for database in sorted(self.grouped_data.keys()):
            # 计算该数据库的统计信息
            req_count = len(self.grouped_data[database])
            record_count = sum(len(records) for records in self.grouped_data[database].values())
            
            # 更简洁大气的标签
            db_display = f"{database.upper()} ({req_count})"
            tab = self.db_tabview.add(db_display)
            self._create_database_view(tab, database)
        
        # 默认选择第一个数据库并初始化
        if self.grouped_data:
            first_db = sorted(self.grouped_data.keys())[0]
            self._initialize_database_view(first_db)
    
    def _on_database_tab_changed(self):
        """数据库选项卡切换时的处理"""
        if not self.db_tabview:
            return
        
        # 获取当前选择的选项卡
        current_tab = self.db_tabview.get()
        if not current_tab:
            return
        
        # 从标签中提取数据库名（格式：ICLAIM (5)）
        database = current_tab.split(' (')[0].lower()
        if database in self.grouped_data:
            self._initialize_database_view(database)
    
    def _initialize_database_view(self, database):
        """初始化数据库视图，选择第一个需求"""
        self.current_database = database
        
        # 获取第一个需求
        requirements = sorted(self.grouped_data[database].keys())
        if requirements:
            first_requirement = requirements[0]
            self.current_requirement = first_requirement
            
            # 找到对应的数据库选项卡并更新按钮状态和SQL显示
            db_display = f"{database.upper()} ({len(self.grouped_data[database])})"
            if self.db_tabview and db_display in self.db_tabview._tab_dict:
                tab_frame = self.db_tabview._tab_dict[db_display]
                
                # 更新按钮状态 - 第一个按钮选中，递归查找
                def find_requirement_buttons(widget):
                    """递归查找requirement_buttons"""
                    if hasattr(widget, 'requirement_buttons'):
                        return widget.requirement_buttons
                    for child in widget.winfo_children():
                        result = find_requirement_buttons(child)
                        if result:
                            return result
                    return None
                
                buttons = find_requirement_buttons(tab_frame)
                if buttons:
                    for i, btn in enumerate(buttons):
                        if i == 0:
                            # 第一个按钮选中状态 - 蓝绿色调
                            btn.configure(
                                fg_color=("#1f9e89", "#0d7f70"),
                                hover_color=("#17a085", "#0a6b5d")
                            )
                        else:
                            # 其他按钮默认状态 - 使用默认按钮颜色
                            btn.configure(
                                fg_color="transparent",
                                hover_color=("#3B8ED0", "#1F6AA5")
                            )
                
                # 找到SQL显示区域并更新 - 递归查找
                def find_sql_display(widget):
                    """递归查找SQL显示区域"""
                    if hasattr(widget, 'sql_textbox'):
                        return widget
                    for child in widget.winfo_children():
                        result = find_sql_display(child)
                        if result:
                            return result
                    return None
                
                sql_display = find_sql_display(tab_frame)
                if sql_display:
                    self._refresh_sql_display(sql_display, database)
    
    def _create_database_view(self, parent, database):
        """创建数据库视图"""
        # 创建左右分栏
        main_container = ctk.CTkFrame(parent, fg_color="transparent")
        main_container.pack(fill="both", expand=True, padx=15, pady=15)
        
        # 左侧需求列表 - 响应式宽度
        req_list_width = max(180, int(self.root.winfo_width() * 0.15))  # 窗口宽度的15%，最小180px
        left_frame = ctk.CTkFrame(main_container, width=req_list_width, corner_radius=12)
        left_frame.pack(side="left", fill="y", padx=(0, 10))
        left_frame.pack_propagate(False)
        
        # 右侧SQL显示 - 更大
        right_frame = ctk.CTkFrame(main_container, corner_radius=12)
        right_frame.pack(side="right", fill="both", expand=True)
        
        self._create_requirement_list(left_frame, database)
        self._create_sql_display(right_frame, database)
    
    def _create_requirement_list(self, parent, database):
        """创建需求列表"""
        # 标题
        title_label = ctk.CTkLabel(
            parent,
            text=f"需求列表",
            font=ctk.CTkFont(family="Microsoft YaHei", size=14, weight="bold")
        )
        title_label.pack(anchor="w", padx=12, pady=(12, 8))
        
        # 滚动框架
        scroll_frame = ctk.CTkScrollableFrame(parent, corner_radius=8)
        scroll_frame.pack(fill="both", expand=True, padx=12, pady=(0, 12))
        
        # 生成需求按钮 - 更紧凑的设计
        requirements = sorted(self.grouped_data[database].keys())
        requirement_buttons = []
        
        def create_requirement_button_command(db, req, btn_idx):
            """创建需求按钮的命令函数，避免闭包问题"""
            def on_click():
                self._select_requirement(db, req, btn_idx)
            return on_click
        
        for i, req_no in enumerate(requirements):
            items = self.grouped_data[database][req_no]
            
            # 统计DDL和DML数量
            ddl_count = sum(1 for item in items if item['script_type'] == 'DDL')
            dml_count = sum(1 for item in items if item['script_type'] == 'DML')
            
            # 创建需求按钮 - 更紧凑的文本
            req_text = f"{req_no}\nDDL:{ddl_count} DML:{dml_count}"
            
            # 第一个按钮默认选中样式 - 使用蓝绿色调
            if i == 0:
                fg_color = ("#1f9e89", "#0d7f70")  # 蓝绿色选中
                hover_color = ("#17a085", "#0a6b5d")
            else:
                fg_color = "transparent"  # 默认透明
                hover_color = ("#3B8ED0", "#1F6AA5")  # 默认悬停色
            
            req_button = ctk.CTkButton(
                scroll_frame,
                text=req_text,
                command=create_requirement_button_command(database, req_no, i),
                font=ctk.CTkFont(family="Microsoft YaHei", size=11),
                height=45,
                corner_radius=8,
                anchor="center",
                fg_color=fg_color,
                hover_color=hover_color
            )
            req_button.pack(fill="x", pady=1)
            requirement_buttons.append(req_button)
        
        # 保存按钮引用到父容器
        setattr(parent, 'requirement_buttons', requirement_buttons)
    
    def _create_sql_display(self, parent, database):
        """创建SQL显示区域"""
        # 顶部工具栏 - 更大气的设计
        toolbar = ctk.CTkFrame(parent, height=55, corner_radius=12)
        toolbar.pack(fill="x", padx=15, pady=(15, 8))
        toolbar.pack_propagate(False)
        
        # 当前选择显示
        selection_label = ctk.CTkLabel(
            toolbar,
            text="💻 请选择左侧需求查看SQL脚本",
            font=ctk.CTkFont(family="Microsoft YaHei", size=15, weight="bold")
        )
        selection_label.pack(side="left", padx=20, pady=17)
        
        # RollBack切换按钮
        rollback_var = ctk.BooleanVar()
        rollback_switch = ctk.CTkSwitch(
            toolbar,
            text="RollBack脚本",
            variable=rollback_var,
            font=ctk.CTkFont(family="Microsoft YaHei", size=13, weight="bold"),
            switch_width=50,
            switch_height=25
        )
        rollback_switch.pack(side="right", padx=20, pady=17)
        
        # 保存引用以便后续更新
        setattr(parent, 'selection_label', selection_label)
        setattr(parent, 'rollback_var', rollback_var)
        setattr(parent, 'rollback_switch', rollback_switch)
        
        # SQL文本显示区域 - 使用微软雅黑字体，支持语法高亮
        sql_textbox = ctk.CTkTextbox(
            parent,
            font=ctk.CTkFont(family="Microsoft YaHei", size=13),
            wrap="word",
            corner_radius=12,
            border_width=2
        )
        sql_textbox.pack(fill="both", expand=True, padx=15, pady=(0, 15))
        
        # 配置SQL语法高亮的标签
        self._configure_sql_syntax_highlighting(sql_textbox)
        
        # 保存引用
        setattr(parent, 'sql_textbox', sql_textbox)
        
        # 初始化显示
        initial_text = f"""-- {database.upper()} 数据库SQL脚本
-- 请从左侧选择需求号查看详细SQL脚本

💡 使用说明:
• 点击左侧需求号按钮查看对应的SQL脚本
• 使用右上角开关切换查看RollBack脚本
• SQL脚本按以下格式显示:
  -- 数据库名
  -- 需求号 备注信息
  SQL语句内容

🔄 RollBack脚本功能:
• 开启开关后显示回滚脚本
• 没有回滚脚本的记录会显示提示信息"""

        sql_textbox.insert("0.0", initial_text)
        sql_textbox.configure(state="disabled")
        
        # 绑定切换事件
        rollback_switch.configure(command=lambda: self._refresh_sql_display(parent, database))
    
    def _configure_sql_syntax_highlighting(self, textbox):
        """配置SQL语法高亮"""
        # SQL关键字
        sql_keywords = [
            'SELECT', 'FROM', 'WHERE', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER',
            'TABLE', 'INDEX', 'VIEW', 'DATABASE', 'SCHEMA', 'PRIMARY', 'KEY', 'FOREIGN',
            'REFERENCES', 'NOT', 'NULL', 'UNIQUE', 'DEFAULT', 'CHECK', 'CONSTRAINT',
            'JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'OUTER', 'ON', 'AS', 'DISTINCT',
            'ORDER', 'BY', 'GROUP', 'HAVING', 'LIMIT', 'OFFSET', 'UNION', 'ALL',
            'AND', 'OR', 'IN', 'EXISTS', 'BETWEEN', 'LIKE', 'IS', 'CASE', 'WHEN',
            'THEN', 'ELSE', 'END', 'IF', 'WHILE', 'FOR', 'DECLARE', 'SET',
            'VARCHAR', 'INT', 'INTEGER', 'BIGINT', 'DECIMAL', 'FLOAT', 'DOUBLE',
            'DATE', 'DATETIME', 'TIMESTAMP', 'TEXT', 'BLOB', 'BOOLEAN'
        ]
        
        # 配置语法高亮标签 - 适配深色主题的颜色
        # SQL关键字 - 亮蓝色
        textbox.tag_config("keyword", foreground="#5dade2")
        # 注释 - 亮绿色
        textbox.tag_config("comment", foreground="#58d68d")
        # 字符串 - 橙色
        textbox.tag_config("string", foreground="#f39c12")
        # 数字 - 紫色
        textbox.tag_config("number", foreground="#bb8fce")
        # 操作符 - 红色
        textbox.tag_config("operator", foreground="#ec7063")
        # 分隔符 - 灰色
        textbox.tag_config("separator", foreground="#aab7b8")
        
        # 保存关键字列表供后续使用
        setattr(textbox, 'sql_keywords', sql_keywords)
    
    def _apply_sql_syntax_highlighting(self, textbox, content):
        """应用SQL语法高亮"""
        import re
        
        # 清除所有现有标签
        for tag in ["keyword", "comment", "string", "number", "operator", "separator"]:
            textbox.tag_delete(tag)
        
        # 重新配置标签（确保在dark mode下也能正确显示） - CustomTkinter不支持font选项
        textbox.tag_config("keyword", foreground="#5dade2")
        textbox.tag_config("comment", foreground="#58d68d")
        textbox.tag_config("string", foreground="#f39c12")
        textbox.tag_config("number", foreground="#bb8fce")
        textbox.tag_config("operator", foreground="#ec7063")
        textbox.tag_config("separator", foreground="#aab7b8")
        
        lines = content.split('\n')
        
        for line_num, line in enumerate(lines, 1):
            line_start = f"{line_num}.0"
            
            # 高亮注释（以--开头的行）
            if line.strip().startswith('--'):
                line_end = f"{line_num}.end"
                textbox.tag_add("comment", line_start, line_end)
                continue
            
            # 高亮SQL关键字
            if hasattr(textbox, 'sql_keywords'):
                for keyword in textbox.sql_keywords:
                    # 使用正则表达式匹配完整单词
                    pattern = r'\b' + re.escape(keyword) + r'\b'
                    for match in re.finditer(pattern, line, re.IGNORECASE):
                        start_pos = f"{line_num}.{match.start()}"
                        end_pos = f"{line_num}.{match.end()}"
                        textbox.tag_add("keyword", start_pos, end_pos)
            
            # 高亮字符串（单引号和双引号）
            string_patterns = [r"'[^']*'", r'"[^"]*"']
            for pattern in string_patterns:
                for match in re.finditer(pattern, line):
                    start_pos = f"{line_num}.{match.start()}"
                    end_pos = f"{line_num}.{match.end()}"
                    textbox.tag_add("string", start_pos, end_pos)
            
            # 高亮数字
            number_pattern = r'\b\d+\.?\d*\b'
            for match in re.finditer(number_pattern, line):
                start_pos = f"{line_num}.{match.start()}"
                end_pos = f"{line_num}.{match.end()}"
                textbox.tag_add("number", start_pos, end_pos)
            
            # 高亮操作符
            operator_patterns = [r'[=<>!]+', r'[+\-*/]', r'[(),;]']
            for pattern in operator_patterns:
                for match in re.finditer(pattern, line):
                    start_pos = f"{line_num}.{match.start()}"
                    end_pos = f"{line_num}.{match.end()}"
                    textbox.tag_add("operator", start_pos, end_pos)
    
    def _select_requirement(self, database, requirement_no, button_index=None):
        """选择需求"""
        self.current_database = database
        self.current_requirement = requirement_no
        
        # 找到对应的数据库选项卡
        db_display = f"{database.upper()} ({len(self.grouped_data[database])})"
        
        if self.db_tabview and db_display in self.db_tabview._tab_dict:
            tab_frame = self.db_tabview._tab_dict[db_display]
            
            # 更新按钮状态 - 递归查找requirement_buttons
            if button_index is not None:
                def find_requirement_buttons(widget):
                    """递归查找requirement_buttons"""
                    if hasattr(widget, 'requirement_buttons'):
                        return widget.requirement_buttons
                    for child in widget.winfo_children():
                        result = find_requirement_buttons(child)
                        if result:
                            return result
                    return None
                
                buttons = find_requirement_buttons(tab_frame)
                if buttons:
                    for i, btn in enumerate(buttons):
                        if i == button_index:
                            # 选中状态 - 蓝绿色调
                            btn.configure(
                                fg_color=("#1f9e89", "#0d7f70"),
                                hover_color=("#17a085", "#0a6b5d")
                            )
                        else:
                            # 默认状态 - 使用默认按钮颜色
                            btn.configure(
                                fg_color="transparent",
                                hover_color=("#3B8ED0", "#1F6AA5")
                            )
            
            # 找到SQL显示区域并更新 - 递归查找
            def find_sql_display(widget):
                """递归查找SQL显示区域"""
                if hasattr(widget, 'sql_textbox'):
                    return widget
                for child in widget.winfo_children():
                    result = find_sql_display(child)
                    if result:
                        return result
                return None
            
            sql_display = find_sql_display(tab_frame)
            if sql_display:
                self._refresh_sql_display(sql_display, database)
    
    def _refresh_sql_display(self, parent, database):
        """刷新SQL显示"""
        if not self.current_requirement:
            return
            
        if self.current_database != database:
            return
        
        requirement_no = self.current_requirement
        if requirement_no not in self.grouped_data[database]:
            return
            
        items = self.grouped_data[database][requirement_no]
        show_rollback = parent.rollback_var.get()
        
        # 更新选择标签
        parent.selection_label.configure(
            text=f"💻 {database.upper()} - {requirement_no} ({len(items)}条记录)"
        )
        
        # 生成SQL内容
        sql_content = f"-- {database.upper()}\n"
        sql_content += f"-- {requirement_no}\n\n"
        
        # 按脚本类型分组显示
        ddl_items = [item for item in items if item['script_type'] == 'DDL']
        dml_items = [item for item in items if item['script_type'] == 'DML']
        
        if ddl_items:
            sql_content += "-- ===================\n-- DDL 脚本\n-- ===================\n\n"
            for item in ddl_items:
                sql_content += self._format_sql_item(item, show_rollback)
                sql_content += "\n\n"
        
        if dml_items:
            sql_content += "-- ===================\n-- DML 脚本\n-- ===================\n\n"
            for item in dml_items:
                sql_content += self._format_sql_item(item, show_rollback)
                sql_content += "\n\n"
        
        # 更新文本框
        parent.sql_textbox.configure(state="normal")
        parent.sql_textbox.delete("0.0", "end")
        parent.sql_textbox.insert("0.0", sql_content)
        
        # 应用SQL语法高亮
        self._apply_sql_syntax_highlighting(parent.sql_textbox, sql_content)
        
        parent.sql_textbox.configure(state="disabled")
    
    def _format_sql_item(self, item, show_rollback=False):
        """格式化单个SQL项"""
        if show_rollback:
            if item['rollback_script']:
                content = f"-- =====================================\n"
                content += f"-- 数据库: {item['database'].upper()}\n"
                content += f"-- 需求号: {item['requirement_no']}\n"
                content += f"-- 备注: {item['remark'] if item['remark'] else '无'}\n"
                content += f"-- 类型: {item['script_type']} RollBack脚本\n"
                content += f"-- =====================================\n\n"
                
                # 格式化RollBack脚本
                rollback_lines = item['rollback_script'].strip().split('\n')
                formatted_rollback = []
                for line in rollback_lines:
                    if line.strip():
                        formatted_rollback.append(line)
                    else:
                        formatted_rollback.append("")
                
                content += '\n'.join(formatted_rollback)
            else:
                content = f"-- =====================================\n"
                content += f"-- 数据库: {item['database'].upper()}\n"
                content += f"-- 需求号: {item['requirement_no']}\n"
                content += f"-- 备注: {item['remark'] if item['remark'] else '无'}\n"
                content += f"-- 类型: {item['script_type']}\n"
                content += f"-- =====================================\n\n"
                content += "💡 此记录没有提供RollBack脚本"
        else:
            content = f"-- =====================================\n"
            content += f"-- 数据库: {item['database'].upper()}\n"
            content += f"-- 需求号: {item['requirement_no']}\n"
            content += f"-- 备注: {item['remark'] if item['remark'] else '无'}\n"
            content += f"-- 类型: {item['script_type']} 脚本\n"
            content += f"-- =====================================\n\n"
            
            # 格式化SQL脚本
            sql_lines = item['sql_script'].strip().split('\n')
            formatted_sql = []
            for line in sql_lines:
                if line.strip():
                    formatted_sql.append(line)
                else:
                    formatted_sql.append("")
            
            content += '\n'.join(formatted_sql)
        
        return content
    
    def _on_parse_error(self, error_message):
        """解析失败处理"""
        self._set_parsing_state(False)
        self.progress_label.configure(text="❌ 解析失败", text_color=("#dc3545", "#e74c3c"))
        messagebox.showerror("❌ 解析失败", f"处理失败:\n{error_message}")
    
    def _update_step_indicator(self, step):
        """更新步骤指示器"""
        self.current_step = step
        for i, (circle, label) in enumerate(self.step_labels, 1):
            if i <= step:
                circle.configure(fg_color="#28a745")
                label.configure(text_color="#ffffff")
            else:
                circle.configure(fg_color="#6c757d")
                label.configure(text_color="#cccccc")
    
    def _open_number_browser(self):
        """打开编号获取网页"""
        import webbrowser
        url = "https://docs.qq.com/sheet/DVG16V0xzc2p0UHFz?tab=h0tmjm&_t=1750303614193&nlc=1"
        webbrowser.open(url)
    
    def _browse_save_location(self):
        """选择保存位置"""
        from tkinter import filedialog
        folder_path = filedialog.askdirectory(
            title="选择保存位置",
            initialdir=self.config.get("last_save_dir", ".")
        )
        if folder_path:
            self.save_path_var.set(folder_path)
            # 保存最后使用的目录
            self.config["last_save_dir"] = folder_path
            self._save_config()
    
    def _show_task_history(self):
        """显示任务历史记录"""
        if "recent_tasks" not in self.config or not self.config["recent_tasks"]:
            messagebox.showinfo("提示", "没有历史任务记录")
            return
            
        # 创建一个临时的顶层窗口
        history_window = ctk.CTkToplevel(self.root)
        history_window.title("历史任务")
        history_window.geometry("300x250")
        history_window.resizable(False, False)
        history_window.grab_set()  # 模态窗口
        
        # 创建标题
        title_label = ctk.CTkLabel(
            history_window,
            text="选择历史任务",
            font=ctk.CTkFont(family="Microsoft YaHei", size=16, weight="bold")
        )
        title_label.pack(pady=(15, 10))
        
        # 创建滚动框架
        scroll_frame = ctk.CTkScrollableFrame(history_window)
        scroll_frame.pack(fill="both", expand=True, padx=15, pady=(0, 15))
        
        # 添加历史任务按钮
        for task in self.config["recent_tasks"]:
            btn = ctk.CTkButton(
                scroll_frame,
                text=task,
                command=lambda t=task: self._select_history_item("task", t, history_window),
                font=ctk.CTkFont(family="Microsoft YaHei", size=12),
                height=35,
                anchor="w"
            )
            btn.pack(fill="x", pady=2)
    
    def _show_name_history(self):
        """显示名字缩写历史记录"""
        if "recent_names" not in self.config or not self.config["recent_names"]:
            messagebox.showinfo("提示", "没有历史缩写记录")
            return
            
        # 创建一个临时的顶层窗口
        history_window = ctk.CTkToplevel(self.root)
        history_window.title("历史缩写")
        history_window.geometry("300x250")
        history_window.resizable(False, False)
        history_window.grab_set()  # 模态窗口
        
        # 创建标题
        title_label = ctk.CTkLabel(
            history_window,
            text="选择历史缩写",
            font=ctk.CTkFont(family="Microsoft YaHei", size=16, weight="bold")
        )
        title_label.pack(pady=(15, 10))
        
        # 创建滚动框架
        scroll_frame = ctk.CTkScrollableFrame(history_window)
        scroll_frame.pack(fill="both", expand=True, padx=15, pady=(0, 15))
        
        # 添加历史缩写按钮
        for name in self.config["recent_names"]:
            btn = ctk.CTkButton(
                scroll_frame,
                text=name,
                command=lambda n=name: self._select_history_item("name", n, history_window),
                font=ctk.CTkFont(family="Microsoft YaHei", size=12),
                height=35,
                anchor="w"
            )
            btn.pack(fill="x", pady=2)
    
    def _select_history_item(self, item_type, value, window):
        """选择历史记录项目"""
        if item_type == "task":
            self.task_entry.delete(0, "end")
            self.task_entry.insert(0, value)
        elif item_type == "name":
            self.name_entry.delete(0, "end")
            self.name_entry.insert(0, value)
        
        # 关闭窗口
        window.destroy()
    
    def _save_config(self):
        """保存配置到文件"""
        ConfigManager.save_config(self.config)
        
    def _on_package_click(self):
        """打包按钮点击处理"""
        # 获取参数
        number = self.number_entry.get().strip()
        task = self.task_entry.get().strip()
        name = self.name_entry.get().strip()
        env = self.env_var.get()
        save_path = self.save_path_var.get().strip()
        
        # 验证参数
        if not number:
            self._show_error("请输入编号")
            return
        if not task:
            self._show_error("请输入任务")
            return
        if not name:
            self._show_error("请输入名字缩写")
            return
        if save_path == "选择保存位置..." or not save_path:
            self._show_error("请选择保存位置")
            return
            
        # 保存任务和缩写到历史记录
        ConfigManager.add_recent_item(self.config, "recent_tasks", task)
        ConfigManager.add_recent_item(self.config, "recent_names", name)
        self._save_config()
        
        # 开始打包
        self._start_packaging(number, task, name, env, save_path)
    
    def _start_packaging(self, number, task, name, env, save_path):
        """开始打包处理"""
        self.package_button.configure(state="disabled", text="📦 正在打包...")
        self.progress_label.configure(text="📦 正在生成打包文件...", text_color=("#f39c12", "#e67e22"))
        self.progress_bar.set(0.2)
        
        # 在后台线程中执行打包
        import threading
        
        def package_thread():
            try:
                # 执行打包
                result = self._create_packages(number, task, name, env, save_path)
                # 回到主线程更新UI
                self.root.after(0, lambda: self._on_package_success(result))
            except Exception as e:
                # 回到主线程显示错误
                self.root.after(0, lambda: self._on_package_error(str(e)))
        
        thread = threading.Thread(target=package_thread, daemon=True)
        thread.start()
    
    def _create_packages(self, number, task, name, env, save_path):
        """创建打包文件"""
        import os
        import zipfile
        from pathlib import Path
        
        # 使用用户选择的保存位置
        save_dir = Path(save_path)
        
        try:
            # 只生成选择的环境包
            results = self._create_environment_package(save_dir, env, number, task, name)
            
            return {
                "env": env,
                "results": results,
                "save_dir": save_dir
            }
        except Exception as e:
            raise e
    
    def _create_environment_package(self, temp_dir, env, number, task, name):
        """创建指定环境的打包文件"""
        from pathlib import Path
        import zipfile
        
        # 环境配置
        env_config = {
            "uat": {
                "server": "awgidsvclclm001",
                "db_prefix": "uat_"
            },
            "prod": {
                "server": "pwgidsvclclm001", 
                "db_prefix": ""
            }
        }
        
        config = env_config[env]
        
        # 创建主包和回滚包
        main_folder = f"CLAIM-{config['server']}-{number}"
        rollback_folder = f"CLAIM-{config['server']}-{number}-rollback"
        
        main_path = temp_dir / main_folder
        rollback_path = temp_dir / rollback_folder
        
        main_path.mkdir(exist_ok=True)
        rollback_path.mkdir(exist_ok=True)
        
        # 按数据库分组处理
        for database in self.grouped_data:
            db_name = config['db_prefix'] + database
            
            # 创建数据库文件夹
            main_db_path = main_path / db_name
            rollback_db_path = rollback_path / db_name
            
            main_db_path.mkdir(exist_ok=True)
            rollback_db_path.mkdir(exist_ok=True)
            
            # 收集该数据库的所有数据
            db_items = []
            for req_no, items in self.grouped_data[database].items():
                db_items.extend(items)
            
            # 分类DDL和DML
            ddl_items = [item for item in db_items if item['script_type'] == 'DDL']
            dml_items = [item for item in db_items if item['script_type'] == 'DML']
            
            # 生成主包文件
            self._create_sql_files(main_db_path, db_name, ddl_items, dml_items, task, name, False)
            
            # 生成回滚包文件
            self._create_rollback_files(rollback_db_path, db_name, ddl_items, dml_items, task, name)
        
        # 创建ZIP文件
        main_zip = temp_dir / f"{main_folder}.zip"
        rollback_zip = temp_dir / f"{rollback_folder}.zip"
        
        self._create_zip_file(main_path, main_zip)
        self._create_zip_file(rollback_path, rollback_zip)
        
        return {
            "main_zip": main_zip,
            "rollback_zip": rollback_zip,
            "main_folder": main_folder,
            "rollback_folder": rollback_folder
        }
    
    def _create_sql_files(self, db_path, database, ddl_items, dml_items, task, name, is_rollback):
        """创建SQL文件"""
        file_counter = 1
        
        # 如果有DDL，先创建DDL文件
        if ddl_items:
            ddl_filename = f"{file_counter:02d}_claim_{database}_ddl_{task}_{name}.sql"
            if is_rollback:
                ddl_filename = f"{file_counter:02d}_claim_{database}_ddl_{task}_{name}_rollback.sql"
            
            ddl_content = self._generate_sql_content(ddl_items, is_rollback)
            with open(db_path / ddl_filename, 'w', encoding='utf-8') as f:
                f.write(ddl_content)
            
            file_counter += 1
        
        # 创建DML文件
        if dml_items:
            dml_filename = f"{file_counter:02d}_claim_{database}_dml_{task}_{name}.sql"
            if is_rollback:
                dml_filename = f"{file_counter:02d}_claim_{database}_dml_{task}_{name}_rollback.sql"
            
            dml_content = self._generate_sql_content(dml_items, is_rollback)
            with open(db_path / dml_filename, 'w', encoding='utf-8') as f:
                f.write(dml_content)
        
        # 创建proc.txt文件
        proc_content = self._generate_proc_content(database, ddl_items, dml_items, task, name, is_rollback)
        with open(db_path / "proc.txt", 'w', encoding='utf-8') as f:
            f.write(proc_content)
    
    def _create_rollback_files(self, db_path, database, ddl_items, dml_items, task, name):
        """创建回滚文件（顺序相反）"""
        file_counter = 1
        
        # 回滚时，DML在前，DDL在后
        if dml_items:
            dml_filename = f"{file_counter:02d}_claim_{database}_dml_{task}_{name}_rollback.sql"
            dml_content = self._generate_sql_content(dml_items, True)
            with open(db_path / dml_filename, 'w', encoding='utf-8') as f:
                f.write(dml_content)
            file_counter += 1
        
        if ddl_items:
            ddl_filename = f"{file_counter:02d}_claim_{database}_ddl_{task}_{name}_rollback.sql"
            ddl_content = self._generate_sql_content(ddl_items, True)
            with open(db_path / ddl_filename, 'w', encoding='utf-8') as f:
                f.write(ddl_content)
        
        # 创建proc.txt文件
        proc_content = self._generate_proc_content(database, ddl_items, dml_items, task, name, True)
        with open(db_path / "proc.txt", 'w', encoding='utf-8') as f:
            f.write(proc_content)
    
    def _generate_sql_content(self, items, is_rollback):
        """生成SQL文件内容"""
        content = ""
        for item in items:
            if is_rollback:
                if item['rollback_script']:
                    content += f"-- {item['database']}\n"
                    content += f"-- {item['requirement_no']} {item['remark']}\n"
                    content += f"-- RollBack脚本\n"
                    content += item['rollback_script'] + "\n\n"
            else:
                content += f"-- {item['database']}\n"
                content += f"-- {item['requirement_no']} {item['remark']}\n"
                content += item['sql_script'] + "\n\n"
        
        return content.strip()
    
    def _generate_proc_content(self, database, ddl_items, dml_items, task, name, is_rollback):
        """生成proc.txt内容"""
        # 使用你提供的proc.txt内容
        content = """rem Loading Scripts Generated (LiuLei S3B&CL) 5/27/2017 sql filename does not allow space
echo off 
echo .................................................................. 
echo usage proc.bat servername databasename 
echo .................................................................. 

date /T >PROCS_%2.OUT 
time /T >>PROCS_%2.OUT 

if "%1"=="" goto USAGE 
if "%2"=="" goto USAGE 


setlocal enabledelayedexpansion
set num=0
set sum=0
for /f "delims=\\" %%a in ('dir /b /a-d /on "*.sql"') do (set /a sum+=1)

for /f "delims=\\" %%a in ('dir /b /a-d /on "*.sql"') do (

set /a num+=1

echo !num! OF !sum! START LOAD: %%a >> PROCS_"%2".OUT
echo !num! OF !sum! START LOAD: %%a 
echo Creating Temp Script... started 
if NOT "%2" == "" echo USE %2 > TempSQL.SQL 
if NOT "%2" == "" echo GO >> TempSQL.SQL 
type %%a  >> TempSQL.SQL 
echo Running Script... %%a
sqlcmd -E -S%1 -f 65001 -iTempSQL.SQL >> PROCS_"%2".OUT
echo !num! OF !sum! END LOAD: %%a 
echo !num! OF !sum! END LOAD: %%a  >> PROCS_"%2".OUT

del TempSQL.SQL 
)

echo Finished. 

date /T >>PROCS_%2.OUT 
time /T >>PROCS_%2.OUT 

EXIT /B 

:USAGE 
echo ERROR ENCOUNTERED ! 
echo PLEASE FOLLOW CORRECT SYNTAX AS SHOWN BELOW : 
echo %0 username password servername databasename 

"""
        return content
    
    def _create_zip_file(self, folder_path, zip_path):
        """创建ZIP文件"""
        import zipfile
        
        with zipfile.ZipFile(zip_path, 'w', zipfile.ZIP_DEFLATED) as zipf:
            for file_path in folder_path.rglob('*'):
                if file_path.is_file():
                    # 计算相对路径，去掉顶层文件夹
                    arcname = file_path.relative_to(folder_path)
                    zipf.write(file_path, arcname)
    
    def _on_package_success(self, result):
        """打包成功处理"""
        self.package_button.configure(state="normal", text="📦 生成打包文件")
        self.progress_label.configure(text="✅ 打包完成！", text_color=("#28a745", "#20c997"))
        self.progress_bar.set(1.0)
        
        # 显示结果
        import tkinter.messagebox as msgbox
        env = result["env"].upper()
        main_zip = result["results"]["main_zip"].name
        rollback_zip = result["results"]["rollback_zip"].name
        save_dir = result["save_dir"]
        
        message = f"打包完成！生成的文件：\n\n"
        message += f"{env}环境：\n• {main_zip}\n• {rollback_zip}\n\n"
        message += f"文件保存在：{save_dir.absolute()}\n\n"
        message += "是否打开文件夹？"
        
        # 询问是否打开文件夹
        open_folder = msgbox.askyesno("打包完成", message)
        if open_folder:
            self._open_folder(save_dir)
    
    def _on_package_error(self, error_msg):
        """打包失败处理"""
        self.package_button.configure(state="normal", text="📦 生成打包文件")
        self.progress_label.configure(text="❌ 打包失败", text_color=("#dc3545", "#e74c3c"))
        self.progress_bar.set(0)
        
        import tkinter.messagebox as msgbox
        msgbox.showerror("打包失败", f"打包过程中出现错误：\n{error_msg}")
    
    def _show_error(self, message):
        """显示错误消息"""
        import tkinter.messagebox as msgbox
        msgbox.showerror("错误", message)
    
    def _open_folder(self, folder_path):
        """打开文件夹"""
        import os
        import subprocess
        import platform
        
        try:
            if platform.system() == "Windows":
                os.startfile(folder_path)
            elif platform.system() == "Darwin":  # macOS
                subprocess.run(["open", folder_path])
            else:  # Linux
                subprocess.run(["xdg-open", folder_path])
        except Exception as e:
            self._show_error(f"无法打开文件夹：{e}")
    
    def _clear_all(self):
        """重置所有内容"""
        # 显示初始界面
        if self.db_tabview:
            self.db_tabview.destroy()
            self.db_tabview = None
        
        self.initial_display.pack(fill="both", expand=True, padx=15, pady=(0, 15))
        
        # 重置变量
        self.selected_file_path = ""
        self.selected_sheet = ""
        self.available_sheets = []
        self.file_path_var.set("请选择Excel文件...")
        self.sheet_combobox.set("请先选择Excel文件")
        
        # 重置sheet页选择器
        self.sheet_combobox.configure(values=["请先选择Excel文件"], state="disabled")
        
        self.parse_button.configure(state="disabled")
        self.package_button.configure(state="disabled", text="📦 生成打包文件")
        self.progress_bar.set(0)
        self.progress_label.configure(text="🟢 就绪", text_color=("#28a745", "#20c997"))
        self._update_step_indicator(1)
        
        # 重置打包参数
        self.number_entry.delete(0, "end")
        self.task_entry.delete(0, "end")
        self.name_entry.delete(0, "end")
        self.env_var.set("uat")
        self.save_path_var.set("选择保存位置...")
        
        # 重置统计
        self.stats_display.configure(text="")
        
        # 清空数据
        self.parsed_data = []
        self.grouped_data = {}
        self.current_database = ""
        self.current_requirement = ""
        self.excel_parser = ExcelParser()
    
    def _show_help(self):
        """显示帮助信息"""
        help_text = """📖 Meiya SQL生成工具使用帮助

📋 新版Excel格式（已更新）:
必须包含6列: 需求号、数据库、脚本类别、SQL脚本、备注、RollBack脚本

✨ 新功能特性:
• 支持多Sheet页Excel文件，可选择指定的Sheet页
• 按数据库自动分类（iclaim, cclaim, adapter）
• 按需求号分组显示DDL/DML统计
• SQL脚本格式化展示
• RollBack脚本切换查看

🚀 使用步骤:
1. 选择包含需求号的Excel文件
2. 选择要处理的Sheet页（自动显示所有可用Sheet页）
3. 点击解析，自动按数据库分类
4. 切换数据库选项卡查看分类结果
5. 点击需求号查看格式化的SQL脚本
6. 使用开关切换查看RollBack脚本

💡 Sheet页选择:
• 选择文件后会自动显示所有Sheet页
• 默认选择第一个Sheet页
• 可以通过下拉框切换不同的Sheet页

💡 SQL格式:
-- 数据库名
-- 需求号 备注信息
SQL语句内容"""
        
        messagebox.showinfo("📖 使用帮助", help_text)
    
    def _on_closing(self):
        """关闭处理"""
        ConfigManager.save_config(self.config)
        self.root.destroy()
    
    def run(self):
        """启动应用"""
        self.root.mainloop()


def main():
    """主函数"""
    try:
        app = MeiyaSQLGenerator()
        app.run()
    except Exception as e:
        print(f"程序启动失败: {e}")
        messagebox.showerror("启动失败", f"程序启动错误:\n{e}")


if __name__ == "__main__":
    main()
